package com.springboot.util.jdbc;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.*;

/**
 * @version 1.0
 * @ClassName MySqlJdbc
 * @Description mysql JDBC连接类
 * @Author zyj
 * @date 2021/3/17 17:10
 **/
@Component
@Slf4j
public class MySqlJdbcUtil {

    private static final String UNICODE2 = "?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useOldAliasMetadataBehavior=true";


    /**
     *
     * @Description jdbc 连接
     * @param userName 用户名
     * @param passwd 用户密码
     * @param databaseUrl 连接地址
     * @param databasePort 连接端口
     * @param tenantDatabase 数据库实例名
     * @param drivername 连接类型
     * @Author zyj
     * @Date 2021/3/17 17:15 
     * @return java.sql.Connection
     **/
    public Connection getConnection(String userName, String passwd, String databaseUrl, String databasePort, String tenantDatabase, String drivername) {

        log.info("数据库连接::::userName:{}, passwd:{}, databaseUrl:{}, databasePort:{}, tenantDatabase:{}, drivername:{}", userName, passwd, databaseUrl, databasePort, tenantDatabase, drivername);
        String url = "jdbc:mysql://"+databaseUrl+":"+databasePort+"/"+tenantDatabase + UNICODE2;

        Connection conn = null;
        try {
            Properties info = new Properties();
            info.setProperty("user", userName);
            info.setProperty("password", passwd);
            Class.forName(drivername);
            conn = DriverManager.getConnection(url, info);
            log.info("数据库连接成功！");
        } catch (Exception e) {
            log.error("数据库连接失败！" , e);
        }

        return conn;
    }


    /**
     * @Description 执行sql
     * @Author zyj
     * @Date 2021/3/17 17:33
     * @param connections 连接
     * @param sql 需要执行的sql
     * @param key 需要获取的数据
     * @return void
     **/
    public List<Map> selectSQLByJDBC(Connection connections, String sql, List<String> key){
        log.info("执行sql:::sql:{},  key:{}", sql, key);
        if(StringUtils.isEmpty(connections) || StringUtils.isEmpty(sql) || null==key || key.isEmpty()){
            return null;
        }

        List<Map> listMap = new ArrayList<>();
        Connection connection = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            connection = connections;
            connection.setAutoCommit(false);
            stmt = connection.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Map resultMap = new HashMap();
                for(String keyStr : key){
                    resultMap.put(keyStr, rs.getObject(keyStr));
                }
                listMap.add(resultMap);
            }

            connection.commit();
        } catch (Exception e) {
            log.error("循环库信息失败", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException e) {
                log.error("循环库信息关闭失败", e);
            }
        }

        return listMap;
    }
}
